create database StoreInStore
use StoreInStore

-- role

create table SISRole
(
	RoleID int identity(1,1) primary key,
	Rolename varchar(30),
)
create table Account
(
	Username varchar(30) primary key,
	Userpass varchar(20),
	Email varchar(50),
	CreatedDate datetime,
	RoleID int foreign key references SISRole(RoleID),
	Active bit
)

create table Shop
(
	ShopID int identity(1,1) primary key,
	ShopOwner varchar(30) foreign key references Account(Username),
	ShopName nvarchar(40),
	LinkName varchar(40),
	Logo varchar(50),
	Slogan nvarchar(80),
	[Address] nvarchar(100),
	Phone varchar(20),
	Active bit,
	CreatedDate datetime,
	ActiveDate datetime,
)

create table ShopKeeper
(
	Username varchar(30) foreign key references Account(Username),
	CMND varchar(12),
	FullName nvarchar(50),
	ContactAddress nvarchar(100),
	Phone varchar(20),
	Active bit
)

create table Customer
(
	Username varchar(30) foreign key references Account(Username),
	FullName nvarchar(50),
	BirthDate date,
	Avatar varchar(50),
	Phone varchar(20),
	Street nvarchar(80),
	District nvarchar(20),
	Point int,
	primary key(username)
)
create table ActivationTicket
(
	TicketID int identity(1,1) primary key,
	TicketCode varchar(10) unique,
	Username varchar(30) foreign key references Account(username),
	CreatedDate datetime,
	ActiveDate datetime
)

create table Category
(
	CateID int identity(1,1) primary key,
	CateName nvarchar(30),
	LinkName varchar(30),
	ParentCate int foreign key references Category(CateID),
	isLeaf bit,
	Active bit	
)

create table PropertyType
(
	TypeID int identity(1,1) primary key,
	CateID int foreign key references Category(CateID),
	PropertyName nvarchar(30),
	IsSelect bit,
	ValidationRule varchar(30)
)

create table Product
(
	ProductID int identity(1,1) primary key,
	ShopID int foreign key references Shop(ShopID),
	CateID int foreign key references Category(CateID),
	Name nvarchar(40),
	pic varchar(50),
	CreateDate datetime,
	Price int,
	Active bit
)

create table ProductPrice
(
	ProductID int foreign key references Product(ProductID),
	ApplyTime datetime,
	SellPrice int,
	primary key(ProductID,ApplyTime)	
)

create table ProductDetail
(
	DetailID int identity(1,1) primary key,
	ProductID int foreign key references Product(ProductID),
	PropertyTypeID int foreign key references PropertyType(TypeID),
	Value nvarchar(20)	
)

create table [Order]
(
	OrderID int identity(1,1) primary key,
	Username varchar(30) foreign key references Account(Username),
	Total int,
	DeliveryName nvarchar(50),
	DeliveryPhone varchar(20),
	DeliveryStreet nvarchar(80),
	DeliveryDistrict nvarchar(20),
	RequestDate datetime,
	[status] varchar(20)
)

create table OrderDetail
(
	OrderDetailID int identity(1,1) primary key,
	OrderID int foreign key references [Order](OrderID),
	ProductID int foreign key references Product(ProductID),
	Quantity int,
	Price int,
	[status] varchar(20)
)
create table OrderDetailSpecific
(
	SpecificID int identity(1,1) primary key,
	OrderDetailID int foreign key references OrderDetail(OrderDetailID),
	ProductID int  foreign key references Product(ProductID),
	DetailID int foreign key references ProductDetail(DetailID)
)

create table Rating
(
	RateID int identity(1,1) primary key,
	Username varchar(30) foreign key references Account(Username),
	ProductID int foreign key references Product(ProductID),
	Score float,
	RateDate datetime
)
drop view V_Product
Create View [V_Product]
AS
	SELECT p.*,pp.SellPrice,s.ShopName,s.LinkName,c.CateName,c.LinkName as LinkCateName,c.ParentCate,Round(((p.Price- pp.SellPrice)/p.Price) * 100,0) as DealPercentage, 'AveScore' = CASE myTable.NumOfRate WHEN 0 THEN 0 ELSE myTable.TotalScore /myTable.NumOfRate END,myTable.TotalScore,myTable.NumOfRate
	FROM
		(SELECT p.ProductID,MAX(pp.ApplyTime) as ApplyTime
		FROM Product p, ProductPrice pp
		WHERE p.ProductID = pp.ProductID and pp.ApplyTime <= GetDate()	
		Group BY p.ProductID) InPrice,
		Product p,
		ProductPrice pp,
		(SELECT p.ProductID, SUM(ISNULL(r.Score,0)) as TotalScore, Count(r.RateID) as NumOfRate
		FROM Product p left outer join Rating r on p.ProductID = r.ProductID
		group by p.ProductID) myTable,
		Shop s,
		Category c
	WHERE p.ProductID = pp.ProductID and pp.ProductID =InPrice.ProductID and pp.ApplyTime = InPrice.ApplyTime and  p.ProductID = myTable.ProductID
		and p.ShopID = s.ShopID and c.CateID = p.CateID
GO
select * from V_Product